import time
import mysql.connector


def save_nucleic_acid_to_sql(name, identity, result, ocr_time, organization):  # 核酸信息
    db = mysql.connector.connect(host='127.0.0.1', user='root', passwd='123456', database='ruoyi_vue')
    cursor = db.cursor(buffered=True)
    cursor.execute("select * from sys_user where nick_name='{identity}'".format(identity=identity))
    if info := cursor.fetchone():
        user_id = info[0]
    else:
        return
    cursor.execute("select * from pcr where id='{user_id}'".format(user_id=user_id))
    if cursor.fetchone():
        sql = "update pcr set organization='{organization}',test_time='{time}',outcome='{status}',update_time='{ctime}' " \
              "where id='{user_id}'" \
            .format(ctime=str(time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(time.time()))),
                    organization=organization,
                    time=str(ocr_time),
                    status=('0' if result == '阴性' else '1'), user_id=user_id)
    else:
        sql = "insert into pcr(id, name, id_card, organization, test_time, outcome, create_time, update_time, is_deleted) " \
              "value('{user_id}', '{name}','{idcard}', '{organization}', '{time}', '{status}', '{ctime}','{ctime}','{isd}')" \
            .format(name=name, idcard=identity, status=('0' if result == '阴性' else '1'),
                    time=str(ocr_time), ctime=str(time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(time.time()))),
                    isd=0,
                    organization=organization, user_id=user_id)
    cursor.execute(sql)
    db.commit()
    db.close()


def save_trip_code_to_sql(recent_region, phone, update_time, filename):  # 行程码信息
    db = mysql.connector.connect(host='127.0.0.1', user='root', passwd='123456', database='ruoyi_vue')
    cursor = db.cursor(buffered=True)
    cursor.execute("select * from files where files='{filename}'".format(filename=filename))
    info = cursor.fetchone()
    if phone[:3] == info[3][:3] and phone[-4:] == info[3][-4:]:
        name = info[1]
        identity = info[2]
        phone = info[3]
    else:
        return
    cursor.execute("select * from sys_user where nick_name='{identity}'".format(identity=identity))
    if xxx := cursor.fetchone():
        user_id = xxx[0]
    else:
        return
    cursor.execute("select * from road_card where id='{user_id}'".format(user_id=user_id))
    if cursor.fetchone():
        sql = "update road_card set road='{road}',update_time='{update_time}' where id='{user_id}'"\
            .format(road=recent_region, update_time=update_time, user_id=user_id)
    else:
        sql = "insert into road_card(id,name, id_card, phonenumber, road, create_time, update_time, is_deleted) " \
              "value ('{user_id}','{name}','{id_card}','{phonenumber}','{road}','{create_time}','{update_time}','{isd}')" \
            .format(phonenumber=phone, road=recent_region,
                    create_time=str(time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(time.time()))), isd=0,
                    update_time=update_time, name=name, id_card=identity, user_id=user_id)
    cursor.execute(sql)
    db.commit()
    db.close()


def save_health_code_to_sql(identity, color):  # 健康码信息
    db = mysql.connector.connect(host='127.0.0.1', user='root', passwd='123456', database='ruoyi_vue')
    cursor = db.cursor(buffered=True)
    cursor.execute("select * from sys_user where nick_name='{identity}'".format(identity=identity))
    if info := cursor.fetchone():
        name = info[2]
        user_id = info[0]
    else:
        return
    cursor.execute("select * from health_code where id='{user_id}'".format(user_id=user_id))
    if cursor.fetchone():
        sql = "update health_code set color='{color}',update_time='{update_time}' where id='{user_id}'"\
            .format(color=color, update_time=str(time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(time.time()))),
                    user_id=user_id)
    else:
        sql = "insert into health_code(id,name, id_card, color, create_time, update_time, is_deleted) " \
              "value ('{user_id}','{name}','{id_card}','{color}','{create_time}','{create_time}','{isd}')" \
            .format(name=name, id_card=identity, color=color,
                    create_time=str(time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(time.time()))), isd=0,
                    user_id=user_id)
    cursor.execute(sql)
    db.commit()
    db.close()
